<?php
if(isset($_POST['action']))
{
    $db = new Database;
    // User Info
        $auth = Auth::getAuth('current_user');
        $user_id = $auth['id'];
        $company_id =  $auth['company_id'];
        $user_group = $auth['role'];
        $user_groupType = $auth['role_type'];
        $userPositionID = $auth['position_id'];
        $getPosition = $db->query("SELECT * FROM tbposition where id = '$userPositionID'","row");
        
    $page = $_POST['page'];
    $cur_page = $page;
    $page -= 1;
    $per_page = 10; // Per page
    $previous_btn = true;
    $next_btn = true;
    $first_btn = true;
    $last_btn = true;
    $start = $page * $per_page;
    $action = $_POST['action'];
    
    // For Sorting
        $dataSortValue = $_POST['dataSortValue'];
        if(!empty($dataSortValue)){
            if($_POST['counterSort']%2==0){
                $sort = "ORDER BY date DESC";
            }else{
                $sort = "ORDER BY date ASC";
            }
        }else{
            $sort = "ORDER BY form_title";
        }
    if($action=="RequestApprove")
    {
        $count = $db->query("SELECT * FROM tbrequest r
                                    LEFT JOIN tbrequest_process rp on r.id=rp.request_id
                                    LEFT JOIN tbform f on f.id=r.form_id
                                    LEFT JOIN tbset_field sf on sf.companyID=f.company_id
                                    WHERE (".roleApprover($user_groupType,$user_group)."
                                    or rp.approver='".$auth['firstname']." ".$auth['lastname'] ."' or rp.approver='".$getPosition['position']."')
                                    and rp.status=0
                                    and r.status=0 and r.company_id = $company_id $sort","numrows");
        
        $queryRequest = $db->query("SELECT sf.numCol as numCol, sf.colValue_formID as colValue_formID,
                                   r.date as date_request,r.user_id as user_id, f.id as form_id,
                                rp.wf_master_id as wf_master_id, f.form_title as form_title, r.data as data,
                                r.id as request_id,rp.id as requestProcess_id FROM tbrequest r
                                LEFT JOIN tbrequest_process rp on r.id=rp.request_id
                                LEFT JOIN tbform f on f.id=r.form_id
                                LEFT JOIN tbset_field sf on sf.companyID=f.company_id
                                WHERE (".roleApprover($user_groupType,$user_group)."
                                or rp.approver='".$auth['firstname']." ".$auth['lastname'] ."' or rp.approver='".$getPosition['position']."')
                                AND rp.status=0 and r.status=0 and r.company_id = $company_id $sort LIMIT $start, $per_page","array");
        $no_of_paginations = ceil($count / $per_page);
    }elseif($_POST['action']=="search_requestApprove"){
        $value = $_POST['value'];
        $count = $db->query("SELECT * FROM tbrequest r
                                    LEFT JOIN tbrequest_process rp on r.id=rp.request_id
                                    LEFT JOIN tbform f on f.id=r.form_id
                                    LEFT JOIN tbset_field sf on sf.companyID=f.company_id
                                    LEFT JOIN tbset_field tbset_field on tbset_field.companyID=f.company_id
                                    WHERE (".roleApprover($user_groupType,$user_group)."
                                    or rp.approver='".$auth['firstname']." ".$auth['lastname'] ."' or rp.approver='".$getPosition['position']."')
                                    AND rp.status=0 AND r.status=0 AND rp.status=0 and r.status=0 and r.company_id = $company_id
                                    AND f.form_title LIKE '%$value%'
                                    
                                    OR (r.user_id='".getUserID($value)."')
                                    AND (".roleApprover($user_groupType,$user_group)."
                                    or rp.approver='".$auth['firstname']." ".$auth['lastname'] ."' or rp.approver='".$getPosition['position']."')
                                    AND rp.status=0 AND r.status=0 AND rp.status=0 and r.status=0 and r.company_id = $company_id
                                    
                                    ","numrows");
        
        $queryRequest = $db->query("SELECT sf.numCol as numCol, sf.colValue_formID as colValue_formID,
                                   r.date as date_request,r.user_id as user_id, f.id as form_id,
                                rp.wf_master_id as wf_master_id, f.form_title as form_title, r.data as data,
                                r.date as dateRequest,
                                r.id as request_id,rp.id as requestProcess_id FROM tbrequest r
                                LEFT JOIN tbrequest_process rp on r.id=rp.request_id
                                LEFT JOIN tbform f on f.id=r.form_id
                                LEFT JOIN tbset_field sf on sf.companyID=f.company_id
                                
                                WHERE
                                
                                (".roleApprover($user_groupType,$user_group)."
                                or rp.approver='".$auth['firstname']." ".$auth['lastname'] ."')
                                AND rp.status=0 and r.status=0 AND f.form_title LIKE '%$value%'
                                
                                OR (r.user_id='".getUserID($value)."')
                                AND (".roleApprover($user_groupType,$user_group)."
                                or rp.approver='".$auth['firstname']." ".$auth['lastname'] ."' or rp.approver='".$getPosition['position']."')
                                AND rp.status=0 and r.status=0 and r.company_id = $company_id
                                
                                LIMIT $start, $per_page","array");
        $no_of_paginations = ceil($count / $per_page);
    }elseif($_POST['action']=="searchDate"){
        $value = $_POST['value'];
        $split = explode(",", $value);
        $count = $db->query("SELECT * FROM tbrequest r
                                    LEFT JOIN tbrequest_process rp on r.id=rp.request_id
                                    LEFT JOIN tbform f on f.id=r.form_id
                                    LEFT JOIN tbset_field sf on sf.companyID=f.company_id
                                    WHERE (".roleApprover($user_groupType,$user_group)."
                                    or rp.approver='".$auth['firstname']." ".$auth['lastname'] ."' or rp.approver='".$getPosition['position']."')
                                    and rp.status=0 and r.company_id = $company_id
                                    and r.status=0 AND r.date>='$split[0]' AND r.date<='$split[1]'","numrows");
        
        
        $queryRequest = $db->query("SELECT sf.numCol as numCol, sf.colValue_formID as colValue_formID,
                                   r.date as date_request,r.user_id as user_id, f.id as form_id,
                                rp.wf_master_id as wf_master_id, f.form_title as form_title, r.data as data,
                                r.id as request_id,rp.id as requestProcess_id FROM tbrequest r
                                LEFT JOIN tbrequest_process rp on r.id=rp.request_id
                                LEFT JOIN tbform f on f.id=r.form_id
                                LEFT JOIN tbset_field sf on sf.companyID=f.company_id
                                WHERE  (".roleApprover($user_groupType,$user_group)."
                                or rp.approver='".$auth['firstname']." ".$auth['lastname'] ."' or rp.approver='".$getPosition['position']."')
                                AND rp.status=0 and r.status=0 AND r.date>='$split[0]' AND r.date<='$split[1]' and r.company_id = $company_id LIMIT $start, $per_page","array");
        $no_of_paginations = ceil($count / $per_page);
    }
    $page = $page+1;
    $finaldata.= '<table class="table_data">
                        <thead>
                            <tr id="sortRequestApprove" data-location="tblRequestToApprove" data-search="form_title" data-tag-action="RequestApprove" p="'.$page.'" data-element="#requestToApprove_tbl">
                                <th width="180" rel="notAllowed"><input type="checkbox" class="chkReqAppAll"></th>
                                <th width="180">Form Title</th>
                                <th width="180">Requestor</th>';
                                // Get created/ setted form field from the database
                                $field = $db->query("SELECT * FROM tbset_field WHERE companyID='$company_id'","array");
                                $getCreatedfield = $db->query("SELECT * FROM tbset_field WHERE companyID='$company_id'","row");
                                $getCreatedfieldRows = $db->query("SELECT * FROM tbset_field WHERE companyID='$company_id'","numrows");
                                if($getCreatedfieldRows>0){
                                    foreach($field as $dataField){
                                        $HeaderValue = explode(",", $dataField['colTitle']);
                                        $split_getCreatedfield = explode(",", $dataField['colValue_formID']);
                                    }
                                    
                                    $header = array();
                                    $headerSearch = array();
                                    $headerID = array();
                                    $headerValArraySort = array();
                                    ksort($split_getCreatedfield);
                                    foreach($split_getCreatedfield as $k => $v){
                                        $split_getCreatedfield_2 = explode("->", $split_getCreatedfield[$k]);
                                        $HeaderValue_2 = explode("->",$HeaderValue[$k]);
                                        $headerValArraySort[$split_getCreatedfield_2[1]] = $split_getCreatedfield_2[0];
                                        $headerID[$split_getCreatedfield_2[1]] = $split_getCreatedfield_2[1];
                                        $headerValArraySort_2[$split_getCreatedfield_2[1]] = $HeaderValue_2[1];
                                    }
                                    ksort($headerValArraySort_2);
                                        foreach($headerValArraySort_2 as $a => $b){
                                            $finaldata.= "<th width='100'>";
                                                $finaldata.= $b;
                                            $finaldata.= "</th>";
                                        }
                                }
                                    
                                    $finaldata.= '<th width="180">Date</th>';
                                    $finaldata.= '<th width="50" colspan="3">Actions</th>';
    $finaldata.= '          </tr>
                        </thead>
                        <tbody class="requestToApproveBody">';
    
    if($count>0){
        foreach($queryRequest as $dataRequest){
            $finaldata.='
                <tr id="requestRow_'.$dataRequest['request_id'].'" class="requestToApprove_tbl">
                    <td style="text-align:center"><input type="checkbox" class="chkReqApp" id="chkReqApp_'.$dataRequest['request_id']."_".$dataRequest['requestProcess_id'].'" object-id="'.$dataRequest['wf_master_id'].'"></td>
                    <td style="text-align:center">'.$dataRequest['form_title'].'</td>
                    <td style="text-align:center">'.getUserWholeName($dataRequest['user_id']).'</td>';
                        
                            $split_getCreatedfield = explode(",", $dataRequest['colValue_formID']);
                            $fieldsWithValue = explode("~",$dataRequest['data']);
                            
                            
                            $headValue = array();
                                ksort($headerID);
                                ksort($headerValArraySort_2);
                                    foreach($fieldsWithValue as $kv => $headVal){
                                        $fieldsWithValue_2 = explode("->", $fieldsWithValue[$kv]);
                                        if(array_search($fieldsWithValue_2[0], $headerID)==true){
                                            $headValue[$fieldsWithValue_2[0]] = $fieldsWithValue_2[1];
                                        }
                                        
                                    }
                                    ksort($headValue);
                                foreach($headerID as $keyHeadVal => $valHeadKey){
                                    $finaldata.= "<td>";    
                                        $finaldata.= $headValue[$keyHeadVal];
                                    $finaldata.= "</td>";
                                    
                                }
            $finaldata.='            
                    <td style="text-align:center">'.$dataRequest['date_request'].'</td>
                    <td style="text-align:center">
                        <a href="#viewForm" class="icon-file actionUserRequest" id="view_'.$dataRequest['request_id']."_".$dataRequest['requestProcess_id'].'" rel="Open File" title="Open Form" data-toggle="modal" style="outline:none"></a>
                    </td>
                    <td style="text-align:center">
                        <a class="icon-ok actionUserRequest" data-num-col="'.$getCreatedfield['numCol'].'" rel="accept" title="Accept" id="accept_'.$dataRequest['request_id']."_".$dataRequest['requestProcess_id'].'" object-id="'.$dataRequest['wf_master_id'].'" style="cursor: pointer"></a>
                    </td>
                    <td style="text-align:center">
                        <a class="icon-remove actionUserRequest" data-num-col="'.$getCreatedfield['numCol'].'" rel="reject" id="reject_'.$dataRequest['request_id']."_".$dataRequest['requestProcess_id'].'" title="Reject" style="cursor: pointer"></a>
                    </td>
                </tr>';
            /*<td style="text-align:center">
                        <a class="icon-ok actionUserRequest" data-num-col="'.$getCreatedfield['numCol'].'" rel="accept" title="Accept" id="accept_'.$dataRequest['request_id']."_".$dataRequest['requestProcess_id'].'" object-id="'.$dataRequest['wf_master_id'].'" style="cursor: pointer"></a>
                    </td>
                    <td style="text-align:center">
                        <a class="icon-remove actionUserRequest" data-num-col="'.$getCreatedfield['numCol'].'" rel="reject" id="reject_'.$dataRequest['request_id']."_".$dataRequest['requestProcess_id'].'" title="Reject" style="cursor: pointer"></a>
                    </td>*/
	}
        //pagination
        pagination($cur_page,$page,$per_page,$start,$previous_btn,$next_btn,$first_btn,$last_btn,$no_of_paginations,$finaldata,$count,$action,$value);
    }else{
        $numCols = $getCreatedfield['numCol'] + 7;
        $finaldata.='<tr><td colspan="'.$numCols.'"><center>No Record Found</center></td></tr>';
        echo $finaldata;
    }
    
}
?>
<script>
    $(".actionUserRequest").tooltip();
</script>
